package com.example.dblibrary.database.daos;


import android.arch.persistence.room.Dao;
import android.arch.persistence.room.Insert;
import android.arch.persistence.room.OnConflictStrategy;
import android.arch.persistence.room.Query;

import com.example.dblibrary.database.AppDatabase;
import com.example.dblibrary.database.entities.Singer;

import java.util.List;

import io.reactivex.Flowable;

@Dao
public interface SingerDao {


    @Query("SELECT * from " + AppDatabase.table_singer)
    Flowable<List<Singer>> loadSingers();

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    List<Long> insert(Singer... singers);

    /**
     * 获取数据总量
     *
     * @return
     */
    @Query("SELECT COUNT(*) FROM " + AppDatabase.table_singer)
    Flowable<Long> count();


    /**
     * 分页查询
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_singer + " LIMIT :startIndex ,:offset")
    Flowable<List<Singer>> loadSingersByPageOffset(int startIndex, int offset);


    /**
     * 分页查询(类别—地域)
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_singer + " WHERE SINGER_TYPE ='%' || :type || '%' AND SINGER_AREA = '%' || :area || '%' LIMIT :startIndex ,:offset")
    Flowable<List<Singer>> loadSingersInAreaByPageOffset(int startIndex, int offset, String type, String area);


    /**
     * 分页查询(地域-简写)
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_singer + " WHERE SINGER_TYPE ='%' || :type || '%' AND SINGER_AREA = '%' || :area || '%' AND SINGER_FIRST_SPELL LIKE '%' || :spell || '%' LIMIT :startIndex ,:offset")
    Flowable<List<Singer>> loadSingersInAreaSpByPageOffset(int startIndex, int offset, String type, String area, String spell);


    /**
     * 分页查询(地域-手写)
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_singer + " WHERE SINGER_TYPE ='%' || :type || '%' AND SINGER_AREA = '%' || :area || '%' AND SINGER_NAME LIKE '%' || :name || '%' LIMIT :startIndex ,:offset")
    Flowable<List<Singer>> loadSingersInAreaHdByPageOffset(int startIndex, int offset, String type, String area, String name);


    /**
     * 分页查询(简写)
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_singer + " WHERE SINGER_FIRST_SPELL LIKE '%' || :spell || '%' LIMIT :startIndex ,:offset")
    Flowable<List<Singer>> loadSingersInSpByPageOffset(int startIndex, int offset, String spell);


    /**
     * 分页查询(手写)
     *
     * @param startIndex
     * @param offset
     * @return
     */
    @Query("SELECT * from " + AppDatabase.table_singer + " WHERE  SINGER_NAME LIKE '%' || :name || '%' LIMIT :startIndex ,:offset")
    Flowable<List<Singer>> loadSingersInHdByPageOffset(int startIndex, int offset, String name);


}
